#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class ProductsDAL : BaseDAL
	 {
         #region ObjIDataReader

         /// <summary>
         /// Returns a new Products instance filled with the DataReader's current record data 
         /// </summary>
         private Products GetProductsFromReader(IDataReader dr)
         {
             Products obj = new Products();
             obj.ProductID = (dr["ProductID"] is DBNull) ? Int32.MinValue : (Int32)dr["ProductID"];
             obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["AddedDate"];
             obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty : (String)dr["AddedBy"];
             obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue : (Int32)dr["CateID"];
             obj.CompanyID = (dr["CompanyID"] is DBNull) ? Int32.MinValue : (Int32)dr["CompanyID"];
             obj.ProductCode = (dr["ProductCode"] is DBNull) ? String.Empty : (String)dr["ProductCode"];
             obj.Title = (dr["Title"] is DBNull) ? String.Empty : (String)dr["Title"];
             obj.Summary = (dr["Summary"] is DBNull) ? String.Empty : (String)dr["Summary"];
             obj.Description = (dr["Description"] is DBNull) ? String.Empty : (String)dr["Description"];
             obj.SKU = (dr["SKU"] is DBNull) ? String.Empty : (String)dr["SKU"];
             obj.UnitPrice = (dr["UnitPrice"] is DBNull) ? Decimal.MinValue : (Decimal)dr["UnitPrice"];
             obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? String.Empty : (String)dr["ImageUrl"];
             obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32)dr["DisplayOrder"];
             obj.LanguageCode = (dr["LanguageCode"] is DBNull) ? String.Empty : (String)dr["LanguageCode"];
             obj.LinkDemo = (dr["LinkDemo"] is DBNull) ? String.Empty : (String)dr["LinkDemo"];
             obj.VideoDemo = (dr["VideoDemo"] is DBNull) ? String.Empty : (String)dr["VideoDemo"];

             return obj;
         }
         #endregion


	     /// <summary>
	     /// Returns a collection of Products with the data read from the input DataReader
	     /// </summary>
	     private List<Products> GetProductsCollectionFromReader(IDataReader dr)
	     {
		     List<Products> obj = new List<Products>();
		     while(dr.Read())
			     obj.Add(GetProductsFromReader(dr));
		     return obj;
	     }



         public List<Products> AdvanceSearch(string Keyword, int CateID, int CompanyID, int ChatlieuID, int Price, int PageIndex, int PageSize)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 int FromID = ((PageIndex - 1) * PageSize) + 1;
                 int ToID = PageIndex * PageSize;

                 List<Products> list = new List<Products>();

                 string sql = "SELECT Distinct w4u_Products.* FROM w4u_Products, Job_Category Where(ProductID > 0) ";
                 sql += "And (w4u_Products.CateID = Job_Category.CateID) ";

                 if (CateID > 0)
                     sql += "And (Job_Category.CateID = " + CateID + " Or Job_Category.ParentCateID = " + CateID + ") ";

                 if (CompanyID > 0)
                     sql = sql + " And(CompanyID = " + CompanyID + ") ";
                 
                 if (Keyword.Length > 0)
                     sql += "And (Title Like N'%" + Keyword + "%' Or ProductCode = '%" + Keyword + "%') ";

                 if (Price > 0)
                 {
                     if (Price == 1)
                         sql += "And(UnitPrice<1000000) And (UnitPrice > 0) ";
                     if (Price == 2)
                         sql += "And(UnitPrice>=1000000) And (UnitPrice<=2000000) ";
                     if (Price == 3)
                         sql += "And(UnitPrice>=2000000) And (UnitPrice<=3000000) ";
                     if (Price == 4)
                         sql += "And(UnitPrice>=3000000) And (UnitPrice<=4000000) ";
                     if (Price == 5)
                         sql += "And(UnitPrice>=4000000) And (UnitPrice<=5000000) ";
                     if (Price == 6)
                         sql += "And(UnitPrice>=5000000) And (UnitPrice<=6000000) ";
                     if (Price == 7)
                         sql += "And(UnitPrice>=6000000) And (UnitPrice<=7000000) ";
                     if (Price == 8)
                         sql += "And(UnitPrice>=7000000) And (UnitPrice<=8000000) ";
                     if (Price == 9)
                         sql += "And(UnitPrice>=8000000) And (UnitPrice<=9000000) ";
                     if (Price == 10)
                         sql += "And(UnitPrice>=9000000) And (UnitPrice<=10000000) ";
                     if (Price == 11)
                         sql += "And(UnitPrice > 10000000) ";
                 }
                 sql += "ORDER BY ProductID DESC";

                 //System.Web.HttpContext.Current.Response.Write(sql);

                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 if (dr.HasRows)
                 {
                     int stt = 0;
                     while (dr.Read())
                     {
                         stt = stt + 1;
                         if (stt >= FromID && stt <= ToID)
                         {
                             list.Add(GetProductsFromReader(dr));
                         }
                     }
                     dr.Close();
                     myConn.Close();
                 }
                 return list;
             }
         }

         public int GetCountAdvanceSearch(string Keyword, int CateID, int CompanyID, int ChatlieuID, int Price)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 string sql = "SELECT Distinct count(*) FROM w4u_Products, Job_Category Where(ProductID > 0) ";
                 sql += "And (w4u_Products.CateID = Job_Category.CateID) ";

                 if (CateID > 0)
                     sql += "And (Job_Category.CateID = " + CateID + " Or Job_Category.ParentCateID = " + CateID + ") ";

                 if (CompanyID > 0)
                     sql = sql + " And(CompanyID = " + CompanyID + ") ";
                                  
                 if (Keyword.Length > 0)
                     sql += "And (Title Like N'%" + Keyword + "%' Or ProductCode = '%" + Keyword + "%') ";

                 if (Price > 0)
                 {
                     if (Price == 1)
                         sql += "And(UnitPrice<1000000) And (UnitPrice > 0) ";
                     if (Price == 2)
                         sql += "And(UnitPrice>=1000000) And (UnitPrice<=2000000) ";
                     if (Price == 3)
                         sql += "And(UnitPrice>=2000000) And (UnitPrice<=3000000) ";
                     if (Price == 4)
                         sql += "And(UnitPrice>=3000000) And (UnitPrice<=4000000) ";
                     if (Price == 5)
                         sql += "And(UnitPrice>=4000000) And (UnitPrice<=5000000) ";
                     if (Price == 6)
                         sql += "And(UnitPrice>=5000000) And (UnitPrice<=6000000) ";
                     if (Price == 7)
                         sql += "And(UnitPrice>=6000000) And (UnitPrice<=7000000) ";
                     if (Price == 8)
                         sql += "And(UnitPrice>=7000000) And (UnitPrice<=8000000) ";
                     if (Price == 9)
                         sql += "And(UnitPrice>=8000000) And (UnitPrice<=9000000) ";
                     if (Price == 10)
                         sql += "And(UnitPrice>=9000000) And (UnitPrice<=10000000) ";
                     if (Price == 11)
                         sql += "And(UnitPrice>10000000) ";
                 }
                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 return (int)myCommand.ExecuteScalar();
             }
         }



         public List<Products> Search(string Keyword, int CateID, int CompanyID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 List<Products> list = new List<Products>();
                 string sql = "Select * from w4u_Products where ProductID > 0 ";
                 if (Keyword.Trim().Length > 0)
                     sql = sql + " And( (Title Like N'%" + Keyword + "%') Or  (Description Like N'%" + Keyword + "%')) ";
                 if (CateID > 0)
                     sql = sql + " And(CateID = " + CateID + ") ";
                 if (CompanyID > 0)
                     sql = sql + " And(CompanyID = " + CompanyID + ") ";

                 sql = sql + " Order by ProductID Desc";

                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetProductsCollectionFromReader(dr);
             }
         }


         public List<Products> GetProductHomePage()
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 List<Products> list = new List<Products>();
                 string sql = "Select top 12 w4u_Products.* from w4u_Products ";
                 sql = sql + " Order by ProductID Desc";
                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetProductsCollectionFromReader(dr);
             }
         }
         

         public List<Products> GetTopProduct(int CateID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 List<Products> list = new List<Products>();
                 string sql = "Select top 6 w4u_Products.* from w4u_Products Where CateID=" + CateID;
                 sql = sql + " Order by ProductID Desc";
                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetProductsCollectionFromReader(dr);
             }
         }


         public int Insert(Products obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_Products_Insert", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Direction = ParameterDirection.Output;
                 myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                 myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = obj.CompanyID;
                 myCommand.Parameters.Add("@ProductCode", SqlDbType.NVarChar).Value = obj.ProductCode;
                 myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                 myCommand.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = obj.Summary;
                 myCommand.Parameters.Add("@Description", SqlDbType.NText).Value = obj.Description;
                 myCommand.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = obj.SKU;
                 myCommand.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obj.UnitPrice;
                 myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                 myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                 myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = obj.LanguageCode;
                 myCommand.Parameters.Add("@LinkDemo", SqlDbType.NVarChar).Value = obj.LinkDemo;
                 myCommand.Parameters.Add("@VideoDemo", SqlDbType.NVarChar).Value = obj.VideoDemo;

                 int ret = myCommand.ExecuteNonQuery();
                 return (int)myCommand.Parameters["@ProductID"].Value;
             }
         }

         
         public bool Update(Products obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_Products_Update", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
                 myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                 myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = obj.CompanyID;
                 myCommand.Parameters.Add("@ProductCode", SqlDbType.NVarChar).Value = obj.ProductCode;
                 myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                 myCommand.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = obj.Summary;
                 myCommand.Parameters.Add("@Description", SqlDbType.NText).Value = obj.Description;
                 myCommand.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = obj.SKU;
                 myCommand.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obj.UnitPrice;
                 myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                 myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                 myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = obj.LanguageCode;
                 myCommand.Parameters.Add("@LinkDemo", SqlDbType.NVarChar).Value = obj.LinkDemo;
                 myCommand.Parameters.Add("@VideoDemo", SqlDbType.NVarChar).Value = obj.VideoDemo;

                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     public bool Delete(Int32 ProductID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the w4u_Products with the specified ID
	     /// </summary>
	     public Products GetProductsByID(Int32 ProductID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetProductsByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetProductsFromReader(dr) : null);
		     }
	     }

	     //Get All Products
	     public List<Products> GetAllProducts(int PageIndex, int PageSize, string LanguageCode)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetAllProducts", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
             myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = LanguageCode;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetProductsCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  Products
	     public int GetCountProducts(string LanguageCode)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetCountProducts", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure;
             myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = LanguageCode;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }
         
	     // Get ProductsByCateID
	     public List<Products> GetProductsByCateID(Int32 CateID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetProductsByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetProductsCollectionFromReader(dr);
		     }
	     }

	     // Get ProductsByCompanyID
	     public List<Products> GetProductsByCompanyID(Int32 CompanyID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetProductsByCompanyID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = CompanyID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetProductsCollectionFromReader(dr);
		     }
	     }

	     // Get ProductsCountByCateID
	     public int GetProductsCountByCateID(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetProductsCountByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }


	     // Get ProductsCountByCompanyID
	     public int GetProductsCountByCompanyID(Int32 CompanyID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Products_GetProductsCountByCompanyID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CompanyID", SqlDbType.Int).Value = CompanyID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
